#!/usr/bin/env python
# -*- coding:utf-8 -*-
#File Name   : monitor_360_backup_state.py
#Author      : moshan
#Mail        : mo_shan@yeah.net
#Created Time: 2020-01-17 17:58:03
#Function    : 监控备份状态
#########################################################################
from datetime import *
import pymysql
import commands
import json
import os
import re
import time
import sys
#import MySQLdb
#import pymongo
from optparse import OptionParser
#from fabric.api import *
#from exchangelib import DELEGATE, Account, Credentials, Configuration, NTLM, Message, Mailbox, HTMLBody
from exchangelib import DELEGATE, Account, Credentials, Configuration, NTLM, Message, Mailbox, HTMLBody
from exchangelib.protocol import BaseProtocol, NoVerifyHTTPAdapter

reload(sys)
sys.setdefaultencoding('utf8')

#配置邮件参数
mail_addr = 'moshan@360.cn'
#mail_to_addr = ['liusainan@360.cn','358181576@qq.com']
mail_user = r'CORP\moshan'
mail_password = 'Actionsky0821!@'
mail_server = 'mail.corp.qihoo.net'

def _args_parse():

    #传入-p参数作为端口号,以此为依据升级整个端口号下的节点,传入ssh登录各个机器所用的用户名和密码
    opt = OptionParser(usage="it's usage tip.",version="%prog 1.0",description=" %prog -s 'subject' -f 'path/file' -t 'xxx@xx.com,zzz@zz.cn,yyy@yy.net'")
    opt.add_option('-s', '--mail_subject', dest="mail_subject", help="-s a subject for email is required !!!")
    opt.add_option('-t', '--mail_to_addr',dest="mail_to_addr", help='send to email address,is required !!!')
    options,args = opt.parse_args()
    if options.mail_subject is None or options.mail_to_addr is None:
        print opt.print_help()
        sys.exit(1)
    return options,args


def f_exe_os_comm(comm_str):

    #执行系统命令
    (status,output) = commands.getstatusoutput(comm_str)
    output = output.replace('\n', "; ")
    #print output

    if status == 0 :
        return output
    else :
        return "-1"


def f_get_html_info():

    mysql_host = "10.121.199.254"
    mysql_user = "mydba"
    mysql_password = "4c41b96c7687f66d"
    mysql_db = "zabbix"
    mysql_port = 3873
    t_today = datetime.now()
    t_today = str(t_today)[0:10]
    db = pymysql.connect(mysql_host, mysql_user, mysql_password, mysql_db, mysql_port, charset='utf8')
    cursor = db.cursor()
    cursor.execute("select count(*),0,(select count(*) from (select * from backup_info where concat(port,ip) in (select concat(port,ip) from backup_info where state = 'failed')) bi join backup_policy_new bpe on bi.port = bpe.port where mode = 'fullnc') mysql,(select count(*) from (select * from backup_info where concat(port,ip) in (select concat(port,ip) from backup_info where state = 'failed')) bi join backup_policy_new bpe on bi.port = bpe.port where mode like '%mongodb%') mongodb,(select count(*) from (select * from backup_info where concat(port,ip) in (select concat(port,ip) from backup_info where state = 'failed')) bi join backup_policy_new bpe on bi.port = bpe.port where mode = 'redis') redis,(select count(*) from (select * from backup_info where concat(port,ip) in (select concat(port,ip) from backup_info where state = 'failed')) bi join backup_policy_new bpe on bi.port = bpe.port where mode = 'pika') pika from backup_info where concat(port,ip) not in (select concat(port,ip) from backup_info where state = 'done') and state = 'failed'")
    data = cursor.fetchall()
    error_data = []
    row_count = data[0]                        #mysql表的结果集中的一行数据
    v_failed = row_count[0]                    #第一列是备份失败的总个数
    v_failed_mysql = row_count[2]              #第二列是mysql失败的个数 
    v_failed_mongodb = row_count[3]            #第三列是mongodb失败的个数
    v_failed_redis = row_count[4]              #第四列是redis失败的个数
    v_failed_pika = row_count[5]               #第五列是pika失败的个数
  
    #判断备份失败的个数是否为0，如果为0则退出
    if row_count[0] != row_count[1]:
        cursor.execute("select pi.func,if (bi.state='init','cinit',bi.state) state,concat(bi.ip,':',bi.port) host_port ,bpe.dest_ip,if(bpe.mode='bigmongodb','mongodbbig',bpe.mode) mode from port_info pi join (select * from backup_info where concat(port,ip) not in (select concat(port,ip) from backup_info where state = 'done')) bi on pi.port = bi.port join backup_policy_new bpe on bi.port = bpe.port order by state desc ,mode")
        error_data = cursor.fetchall()
        #查看备份失败的历史记录，主要是判断当前备份失败的是否在历史表中存在，用于统计历史备份失败的次数以及连续备份失败的次数
        cursor.execute("select ip_port from backup_status_count")    
        history_backup_state = cursor.fetchall()
    else :
        sys.exit()

    time_now = str(datetime.now())
    time_now = time_now[0:19]
    tr_str="                    "              #html代码格式化所用
    td_str="                        "          #html代码格式化所用
    html = ""                                  #构造html静态页面
    html_tmp = ""

    for diff_row in error_data:                #遍历备份失败的端口
        error_data_tmp = diff_row              #结果集的一行数据
        v_project_name = error_data_tmp[0]     #第一列数据，业务名称
        v_backup_state = error_data_tmp[1]     #第二列数据，备份状态
        v_ip_port = error_data_tmp[2]          #第三列数据，ip和port信息
        v_dest_ip = error_data_tmp[3]          #第四列数据，备份机的ip地址
        v_node_type = error_data_tmp[4]        #第五列数据，数据库类型信息，mysql/mongodb/redis/pika
        v_ip = v_ip_port.split(':')[0]         #将备份失败的节点的的ip和port拆成单独的变量
        v_port = v_ip_port.split(':')[1]

        v_backup_info = ""                     #一些临时变量
        v_count = 0
        v_sum_count = 0
        v_count_info = ""
        
        table_size = "2000"                    #table的总宽长度
        size_1 = "330"                         #table第一列宽度
        size_2 = "100"                         #定义各列宽度
        size_3 = "80"
        size_4 = "80"
        size_5 = "100"
        size_6 = "100"
        size_7 = "600"

        #第一列是业务名称，拼接html的table的td代码段，也就是列
        diff_table_info_tmp = str(v_project_name.encode('utf-8'))
        project_name = td_str + "<td width='" + size_1 + "' ><strong>" + diff_table_info_tmp + "</strong></td>"

        #同理拼接第二列
        diff_table_info_tmp = str(v_ip_port.encode('utf-8'))
        ip_port = td_str + "<td width='" + size_2 + "' ><strong>" + diff_table_info_tmp + "</strong></td>"

        #同理拼接第三列
        if v_node_type == 'fullnc' :
            node_type = td_str + "<td width='" + size_3 + "' ><strong>MySQL</strong></td>"
        elif v_node_type == 'mongodbbig' :
            node_type = td_str + "<td width='" + size_3 + "' ><strong>bigmongodb</strong></td>"
        else :
            node_type = td_str + "<td width='" + size_3 + "' ><strong>" + v_node_type + "</strong></td>"

        #同理拼接第四列, 第四列如果是失败的话，需要做的事情较多，需要找出失败的原因，以及连续失败的次数，历史失败的次数
        if v_backup_state == 'begin' :
            backup_state = td_str + "<td width='" + size_4 + "' style='background-color:#28FF28'><strong>正在备份</strong></td>"

        elif v_backup_state == 'failed' :
            # 如果备份失败，就会保存备份记录，用于判断连续备份失败的天数
            if len(history_backup_state) == 0 :
                sql = "insert into backup_status_count select '" + v_ip_port + "', 1, 1, '" + time_now[0:10] + "';"
            else :
                v_result = 0
                for ip_port_tmp in history_backup_state :
                    # 如果记录备份状态的表存在这个ip_port的记录则取更新该行
                    # 如果上次备份失败的时间是前一天则count+=1，表示连续失败的天数加一
                    # 如果不是前一天时间则count=1表示第一天失败
                    if v_ip_port == ip_port_tmp[0] :
                        v_result = 1
                        break
                if v_result == 1 :
                    #如果以前有过备份失败的记录，则更新历史备份的次数，判断上一次失败的时间是不是前一天，如果是前一天就将count+1，表示连续失败
                    sql = "update backup_status_count set count = if (update_time = (date_sub(curdate(),interval 1 day)),count + 1, 1), sum_count = if(update_time = (date_sub(curdate(),interval 0 day)),sum_count,sum_count + 1), update_time = '" + time_now[0:10] + "' where ip_port = '" + v_ip_port + "';"
                else :
                    #如果以前没有过备份失败的记录，则直接插入一条记录
                    sql = "insert into backup_status_count select '" + v_ip_port + "', 1, 1, '" + time_now[0:10] + "';"

            try :
                #将备份失败的节点的信息写到表中
                cursor.execute(sql)
                db.commit()
                #error_info = "[ " + time_now + " ] [ 写入成功 ] " + "[ " + sql + " ]"
                #print error_info
            except Exception as e:
                db.rollback()
                error_info = "[ " + time_now + " ] [ 写入失败 ] " + "[ " + sql + " ]" + "[ " + str(e) + " ]"
                print error_info

            #print sql
            #将备份失败的节点做一下简单的判断，先将备份日志拿到本地，做一些分析，如果与如下列举的匹配则将日志输出到邮件中
            backup_info_file = "/home/moshan/backup_info/db_backup_" + v_port
            f_exe_os_comm("sshpass -p 'Ationcsky0821!@' scp moshan@" + v_ip + ":/var/log/db_backup_" + v_port + " " + backup_info_file)
            backup_state = td_str + "<td width='" + size_4 + "' style='background-color:#F75000'><strong>备份失败</strong></td>"

            if os.path.exists(backup_info_file) :

                if f_exe_os_comm("grep -c '{errno:909' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    backup_info = f_exe_os_comm("grep '{errno:909' /home/moshan/backup_info/db_backup_" + v_port)

                    if len(backup_info.split('no authentication from')) == 2 :
                        backup_info = 'no authentication from' + backup_info.split('no authentication from')[1]
                        backup_info = '主机不受Qcmd管控: ' + backup_info
                    else :
                        backup_info = '主机不受Qcmd管控, 备份机:' + v_dest_ip

                elif f_exe_os_comm("grep -c 'Failed to connect to MySQL server' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    backup_info = '备份环境有问题'

                elif f_exe_os_comm("grep -c 'Error: Failed to stream' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    backup_info = '备份机' + v_dest_ip + '网卡带宽跑满了'

                elif f_exe_os_comm("grep -c '{errno:4,errmsg:' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :

                    backup_info = f_exe_os_comm("grep '{errno:4,errmsg:' /home/moshan/backup_info/db_backup_" + v_port)

                    if len(backup_info.split('no authentication from')) == 2 :
                        backup_info = 'no authentication from' + backup_info.split('no authentication from')[1]

                elif f_exe_os_comm("grep -c 'rsync error:' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    backup_info = f_exe_os_comm("grep -B 1 'rsync error:' /home/moshan/backup_info/db_backup_" + v_port)
                    backup_info = backup_info + "; 可能是备份机" + v_dest_ip + "网卡带宽跑满了"

                elif f_exe_os_comm("grep -c 'no authentication from' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    backup_info = f_exe_os_comm("grep 'no authentication from' /home/moshan/backup_info/db_backup_" + v_port)
                    backup_info = 'no authentication from' + backup_info.split('no authentication from')[1]

                elif f_exe_os_comm("grep -c 'Error: failed to read page after .* retries. File' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    backup_info = f_exe_os_comm("grep 'Error: failed to read page after .* retries. File' /home/moshan/backup_info/db_backup_" + v_port)
                    backup_info = '业务表损坏：' + backup_info.split('failed to read page after 10 retries. File')[1].split('seems to be corrupted')[0]

                elif f_exe_os_comm("grep -c 'Error: ibbackup child process has died at' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    if f_exe_os_comm("grep -c '/usr/local/addops/scripts/dba/common/xtrabackup_51' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                        backup_info = 'MySQL5.1环境的备份需要依赖mysql环境，请检查环境变量是否配有MySQL 5.1'

                elif f_exe_os_comm("grep -c 'child process has died at' /home/moshan/backup_info/db_backup_" + v_port) != '-1' :
                    backup_info = '备份机' + v_dest_ip + '网卡带宽跑满了'

                else :
                    backup_info = '其他原因'
            else :
                backup_info = '其他原因'
        elif v_backup_state == 'cinit' :
            backup_state = td_str + "<td width='" + size_4 + "' style='background-color:#FFFF37'><strong>等待备份</strong></td>"

        #向数据库查询连续备份失败的次数以及历史备份失败的次数
        cursor.execute("select count,sum_count from backup_status_count where ip_port = '" + v_ip_port + "'")
        v_count_info = cursor.fetchall()
        for v_count_tmp in v_count_info :
            v_count = v_count_tmp[0]
            v_sum_count = v_count_tmp[1]
        
        #拼接第五列，如果连续备份失败的次数大于等于5次则标红，如果大于等于两次则标黄显示
        if v_count >= 5 :
            count = td_str + "<td width='" + size_5 + "' style='background-color:#F75000'><strong>" + str(v_count) + "</strong></td>" 
        elif v_count >= 2 :
            count = td_str + "<td width='" + size_5 + "' style='background-color:#FFFF37'><strong>" + str(v_count) + "</strong></td>" 
        else :
            count = td_str + "<td width='" + size_5 + "' ><strong>" + str(v_count) + "</strong></td>" 

        #拼接第六列
        sum_count = td_str + "<td width='" + size_6 + "' ><strong>" + str(v_sum_count) + "</strong></td>" 

        #拼接第七列
        if v_backup_state == 'failed' :
            backup_info = td_str + "<td width='" + size_7 + "' ><strong>" + backup_info  + "</strong></td>"
        else :
            backup_info = td_str + "<td width='" + size_7 + "' ><strong>NULL</strong></td>"
       
        #拼接一整行，即tr标签，将以上所有列拼接成一行
        html_tmp += "\n" + tr_str + "<tr>\n" + project_name + "\n" + ip_port + "\n" + node_type + "\n" + backup_state + "\n" + count + "\n" + sum_count + "\n" + backup_info + "\n" + tr_str + "</tr>\n"
    cursor.close()
    db.close()

    html = """<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>备份状态信息展示</title>
    </head>
    <body>
        <div id="container">
            <p>采集时间: """ + time_now + """</p>
            <p><span style="color:red;"><strong>备份状态信息统计, 失败总数""" + str(v_failed) + """</strong></span></p>
            <div id="content">
                <table width="600" border="2" bordercolor="black" cellspacing="2" style="font-family'宋体'; font-size20px;">
                    <tr>
                        <td width='100'><font color="#0000FF"><strong>数据库类型</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>MySQL</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>MongoDB</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>Redis</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>Pika</strong></font></td>
                    </tr>

                    <tr>
                        <td width='100'><font color="#0000FF"><strong>失败的个数</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>""" + str(v_failed_mysql) + """</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>""" + str(v_failed_mongodb) + """</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>""" + str(v_failed_redis) + """</strong></font></td>
                        <td width='100'><font color="#D2691E"><strong>""" + str(v_failed_pika) + """</strong></font></td>
                    </tr>

                </table>
                <p><span style="color:red;"><strong>备份状态信息统计详情</strong></span></p>
                <table width='""" + table_size + """' border="2" bordercolor="black" cellspacing="2" style="font-family'宋体'; font-size50px;">
                    <tr>
                        <td width='""" + size_1 + """' ><font color="#0000FF"><strong>业务名称</strong></font></td>
                        <td width='""" + size_2 + """' ><font color="#0000FF"><strong>IP地址:端口</strong></font></td>
                        <td width='""" + size_3 + """' ><font color="#0000FF"><strong>数据库类型</strong></font></td>
                        <td width='""" + size_4 + """' ><font color="#0000FF"><strong>备份状态</strong></font></td>
                        <td width='""" + size_5 + """' ><font color="#0000FF"><strong>连续失败次数</strong></font></td>
                        <td width='""" + size_6 + """' ><font color="#0000FF"><strong>历史失败次数</strong></font></td>
                        <td width='""" + size_7 + """' ><font color="#0000FF"><strong>失败原因</strong></font></td>
                    </tr>""" + html_tmp + """

                </table>
            </div>
        </div>
    </body>
</html>
        """
    #构造html
    #print html
    return html


def f_send_mail(mail_addr,mail_to_addr,mail_user,mail_password,mail_server,mail_subject,mail_body):

    try:
        cred = Credentials(mail_user, mail_password)
        config = Configuration(server = mail_server, credentials = cred, auth_type = NTLM)
        account = Account(primary_smtp_address = mail_addr, config = config, autodiscover = False, access_type = DELEGATE)
        m = Message(account = account,subject = mail_subject.encode('utf-8'),body = HTMLBody(mail_body),to_recipients = mail_to_addr)
        m.send_and_save()
        log_time = str(datetime.now())
        log_time = log_time[0:19]
        error_info = "[ " + log_time + " ] [ 邮件发送成功 ] "
        print error_info

    except Exception as e:
        log_time = str(datetime.now())
        log_time = log_time[0:19]
        error_info = "[ " + log_time + " ] [ 邮件发送失败,退出 ] [ " + str(e) + " ]"
        print error_info
        sys.exit(1)

    else:
        pass

    finally:
        pass


def f_init_mail_to_addr(mail_to_addr):

    mail_to_addr1 = []
    for i in xrange(len(mail_to_addr)):
        obj = Mailbox(email_address = mail_to_addr[i])
        mail_to_addr1.append(obj)
    return mail_to_addr1



def f_main():

    options,args = _args_parse()
    mail_subject = options.mail_subject
    mail_to_addr = options.mail_to_addr.split(',')
    mail_to_addr1 = f_init_mail_to_addr(mail_to_addr)
    mail_body = f_get_html_info()
    f_send_mail(mail_addr,mail_to_addr1,mail_user,mail_password,mail_server,mail_subject,mail_body)

if __name__ == "__main__":
    f_main()


